insert overwrite table jms_dm.dm_sqs_abnomal_problem_cnt_dt partition(dt)
select date_time as date_time --登记日期
     ,registration_network_code
     ,registration_network_name
     ,reg_network_mr_code
     ,reg_network_mr_name
     ,reg_network_proxy_code
     ,reg_network_proxy_name
     ,proble_type_subject_code
     ,proble_type_subject_name
     ,second_level_type_code
     ,second_level_type_name
     ,order_source_name
     ,in_out_bound
     ,count(1) as problem_cnt --问题件件量
     ,case when proble_type_subject_code='66' then count(if(is_report=1,waybill_no,null))
           when proble_type_subject_code='29' and  second_level_type_code='29a'  then count(1)-count(if(is_error=1,waybill_no,null))
    end as reported_cnt --已报备票数
     ,case when proble_type_subject_code='66' then count(1)-count(if(is_report=1,waybill_no,null))
           when proble_type_subject_code='29' and  second_level_type_code='29a'  then count(if(is_error=1,waybill_no,null))
    end as abnomal_cnt --异常票数
     ,count(if(is_before_pro =1,waybill_no,null)) as before_problem_cnt --问题件前超时效催件
     ,count(if(is_after_pro =1,waybill_no,null)) as after_problem_cnt --问题件后超时效催件
     ,case when proble_type_subject_code='66' then 1 --不可抗力
           when proble_type_subject_code='29' and  second_level_type_code='29a' then 2 --有到未发
    end as cnt_type --统计类型1不可抗力2有到未发
     ,reg_fran_code
     ,reg_fran_name
     ,network_type
     ,dt as dt
FROM jms_dm.dm_sqs_registration_problem_detail_dt
where dt between  date_add('{{ execution_date  | cst_ds }}',-29)  and '{{ execution_date  | cst_ds }}'
  and registration_network_code is not null
  and (proble_type_subject_code='66' --不可抗力因素问题件
    or (proble_type_subject_code='29' and  second_level_type_code='29a')) --有发未到
group by date_time --登记日期
       ,registration_network_code
       ,registration_network_name
       ,reg_network_mr_code
       ,reg_network_mr_name
       ,reg_network_proxy_code
       ,reg_network_proxy_name
       ,proble_type_subject_code
       ,proble_type_subject_name
       ,second_level_type_code
       ,second_level_type_name
       ,order_source_name
       ,in_out_bound
       ,reg_fran_code
       ,reg_fran_name
       ,network_type
       ,dt
    distribute by 1
;